/**
 * Created by Administrator on 2016/4/8.
 */
var msql = require("./../node_modules/mysql");

//创建连接池
var pool = msql.createPool({
    connectionLimit: 10,
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'baijie84',
    port: '3306'
});
exports.createmyPool = function (req, res) {
    pool = msql.createPool({
        connectionLimit: 10,
        host: 'localhost',
        user: 'root',
        password: 'root',
        database: 'baijie84',
        port: '3306'
    });
};

//connection 连接
exports.createMyConnection= function (req, res) {
    var connection = mysql.createConnection({
        host: '127.0.0.1',
        user: 'root',
        password: 'root',
        port: '3306',
        database: 'baijie84'
    });
};

// 封装 pool 连接 传入参数只需要sql
/*  sql = "";
*    poolGetConnection(sql, res);
 */
exports.poolGetConnection = function (sql, res){
    //从连接池中创建一个连接
    pool.getConnection(function (err, connection) {
        //报错执行 的 代码
        if (err) throw err;
        /*执行sql语句*/
        connection.query(sql, function (err, result) {
            console.log("result" + result);
            /*返回数据*/
            res.send(result);
            /*链接释放*/
            connection.release();
        })
    });
};


//demo pool 使用方法
//insert
exports.insertSqlDemo = function (req,res) {
    console.log("*********in insertSqlDemo**********");
    //
    pool.getConnection(function (err, connection) {
        if(err) throw err;
        //查看pool创建的connected id
        console.log('connected as id ' + connection.threadId);
        /*
        要在js中创建id, 表中主键 id 为自增长的时候,在sql 中插入不能为空
        connection.query("INSERT INTO day3_student VALUES(' ','王三','男','2001-10-10','95033')", function(err, result)
        */
        var sid = 10;
        connection.query("INSERT INTO day3_student ('SID''SNAME','SSEX','SBIRTHDAY','CLASS') VALUES('"+sid+"','王三','男','2001-10-10','95033')", function(err, result){
            if(err) throw err;
            //查看result返回的合集
            console.log(result);
            //查看result返回的 被影响的行数
            console.log('connected  affectedRows === ' + result.affectedRows + '  rows');
            //返回成功执行的数据
            res.send(result);

        });

        //调用connection.release()方法，会把连接放回连接池，等待其它使用者使用!
        connection.release();
    });
};


//多表操作
exports.selSqlDemo = function (req,res) {
    console.log("*********in selSqlDemo**********");
    //
    pool.getConnection(function (err, connection) {
        if(err) throw err;

        var sid = 10;
        connection.query("INSERT INTO day3_student ('SID''SNAME','SSEX','SBIRTHDAY','CLASS') VALUES('"+sid+"','王三','男','2001-10-10','95033')", function(err, result){
            if(err) throw err;
            //查看result返回的合集
            console.log(result);
            //查看result返回的 被影响的行数
            console.log('connected  affectedRows === ' + result.affectedRows + '  rows');

            connection.query("INSERT INTO day3_score VALUES('"+sid+"','088','95033')");


            //返回成功执行的数据
            res.send(result);
        });

        //调用connection.release()方法，会把连接放回连接池，等待其它使用者使用!
        connection.release();
    });
};



/*登录注册dao层*/
//验证此手机号码在数据库中是否存在
exports.shouji=function (req,res){
    var phone=req.body.phone;
    var sql="SELECT * FROM hc_user WHERE phone='"+phone+"'";
    pool.getConnection(function (err, connection) {
        //报错执行 的 代码
        if (err) throw err;
        /*执行sql语句*/
        connection.query(sql, function (err, result) {
            console.log("result" + result);
            /*返回数据*/
            res.send(result);
            /*链接释放*/
            connection.release();
        })
    });

};
//登录 在user表查询数据 POST 方法
exports.loginSql = function (req,res) {
    pool.getConnection(function (err, connection) {
        //报错执行 的 代码
        var userPhone = req.body.phone;
        var userPwd = req.body.pwd;
        //console.log(userPhone);
        //console.log(userPwd);
        if (err) throw err;
        /*执行sql语句*/
        var sql ="SELECT * FROM hc_user WHERE phone='"+userPhone+"'AND pwd='"+userPwd+"'";
        connection.query(sql, function (err, result) {
            console.log("result" + result);
            /*返回数据*/
            if(result.length>=1){
                //req.session.a=result[0].user_name;
                req.session.a=userPhone;

                //console.log( req.session.a);
                res.send(result);
            }else{
                res.send(result);
            }
            /*链接释放*/
            connection.release();
        })
    });
};


/*判断用户页面是否登录了*/
exports.mysession = function (req,res,next){
    var myname=req.session.a;
    //console.log(myname);
    if(myname!=null || myname!=undefined){
        //console.log(1);
        //有值的情况下
        res.send("1");
    }else{
        //next();
        //没有值的情况下
        res.send("2");
    }
};
/*进行取出用户的数据*/
exports.myInf = function (req,res){
    var myname=req.session.a;
    //console.log(myname);
    if(myname!=null || myname!=undefined){
        //console.log(1);
        //有值的情况下
        var sql="SELECT user_name FROM hc_user WHERE phone='"+myname+"'";
        pool.getConnection(function (err, connection) {
            //报错执行 的 代码
            if (err) throw err;
            /*执行sql语句*/
            connection.query(sql, function (err, result) {
                console.log("result" + result);
                /*返回数据*/
                res.send(result);
                /*链接释放*/
                connection.release();
            })
        });

        //res.send("1");
    }else{
        //next();
        //没有值的情况下
        //res.send("2");
    }
};


/*注册的页面*/
exports.insert=function (req,res){
    var phone = req.body.phone;
    var pwd = req.body.pwd;
    console.log(phone);
    console.log(pwd);
    pool.getConnection(function (err, connection) {
        //报错执行 的 代码
        if (err) throw err;
        /*执行sql语句*/
        var sql="INSERT INTO hc_user (user_id,phone,pwd) VALUES ("+phone+","+pwd+")";
        connection.query(sql, function (err, result) {
            console.log("result" + result);
            console.log("err+++"+err);
            /*返回数据*/
            res.send(result);
            /*链接释放*/
            connection.release();
        })
    });

};